Crispo - Excel Challenge 50 2025

excel-challenges
weekly-exercises
Easy Sunday Excel Challenge
Published

December 14, 2025

Illustration for Crispo - Excel Challenge 50 2025

Challenge Description

Easy Sunday Excel Challenge

⭐ Problem Solution Month Customer Sales Jan

Solutions

library(tidyverse)
library(readxl)
library(janitor)

path <- "2025-12-14/Challenge 83.xlsx"
input <- read_excel(path, range = "B2:D6")
test <- read_excel(path, range = "F2:H15")

result <- input %>%
  separate_longer_delim(cols = c(Customer, Sales), delim = ", ") %>%
  mutate(Sales = as.numeric(Sales))

summary <- result %>%
  group_by(Month) %>%
  summarize(Sales = sum(Sales, na.rm = TRUE), .groups = "drop") %>%
  mutate(Customer = NA_character_, .before = Sales)

months <- unique(result$Month)
final <- map_dfr(
  months,
  ~ bind_rows(
    result %>% filter(Month == .x),
    summary %>% filter(Month == .x)
  )
) %>%
  mutate(Month = ifelse(is.na(Customer), "Total Sales", Month))

all.equal(final, test)
# [1] TRUE

  • Logic:

    • Reads the workbook range needed for the challenge

    • Reshapes the data to the grain required by the task

    • Aggregates or ranks values at the correct grouping level

    • Builds the intermediate helper columns that drive the final answer

  • Strengths:

    • The R solution stays compact and mirrors the workbook logic closely.

  • Areas for Improvement:

    • The code assumes the workbook layout and named ranges remain stable.

  • Gem:

    • The best part of the solution is choosing a tidy intermediate shape before producing the final answer.
import pandas as pd

df = pd.read_excel("2025-12-14/Challenge 83.xlsx", usecols="B:D", skiprows=1, nrows=4)
test = pd.read_excel("2025-12-14/Challenge 83.xlsx", usecols="F:H", skiprows=1, nrows=14).rename(columns=lambda c: c.replace('.1', ''))

def split_and_align(a, b):
    a = a.split(", ") if isinstance(a, str) and ", " in a else [a]
    b = b.split(", ") if isinstance(b, str) and ", " in b else [b]
    n = max(len(a), len(b))
    return (a * n)[:n], (b * n)[:n]

df[["Customer", "Sales"]] = df.apply(lambda r: pd.Series(split_and_align(r["Customer"], r["Sales"])), axis=1)
df = df.explode(["Customer", "Sales"])
df["Sales"] = pd.to_numeric(df["Sales"], errors="coerce")
def add_subtotals(df):
    out = []
    for m, g in df.groupby("Month", sort=False):
        out.append(g)
        row = {**{c: None for c in df.columns}, "Month": "Total Sales", "Sales": g["Sales"].sum()}
        out.append(pd.DataFrame([row], columns=df.columns))
    return pd.concat(out, ignore_index=True)

df = add_subtotals(df)

print(df.equals(test))

  • Logic:

    • Reads the workbook range needed for the challenge

    • Aggregates or ranks values at the correct grouping level

    • Applies the rule iteratively until the output is complete

  • Strengths:

    • The Python version keeps the same rule in a direct pandas-oriented workflow.

  • Areas for Improvement:

    • As with the R version, any workbook layout change would require small adjustments.

  • Gem:

    • The implementation stays close to the stated challenge instead of adding unnecessary complexity.

Difficulty Level

This task is moderate:

  • It combines familiar Excel-style logic with at least one non-trivial reshape, grouping, or parsing step.

  • The answer depends on getting the output layout exactly right.